package dm;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import database.DBConnection;
import bean.catalog.Catalog;
import bean.catalog.Category;
import bean.catalog.Criteria;
import bean.catalog.SearchedCategory;
import bean.catalog.SubCriteria;
import bean.shoppingCard.Product;

public class CatalogDM {
	
	public ArrayList<Catalog> getCatalogList(String id, String name) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		ResultSet res = null;
		ArrayList<Catalog> catalogList = new ArrayList<Catalog>();
		try {
			statement = con.prepareCall("{?=call CATALOG_PKG.GET_CATALOG_LIST(?)}");
			statement.registerOutParameter(1, OracleTypes.CURSOR);
			statement.setString(2,id);
			statement.execute();
			OracleCallableStatement ocstm = (OracleCallableStatement) statement;
			res = ocstm.getCursor(1);
			
			while(res.next()){
				Category c = new Category(res.getString(1));
				c.setCatalogId(res.getString(2));
				catalogList.add(c);
			}
			
			if(catalogList.size() < 1){
				SearchedCategory cat = new SearchedCategory(name);
				cat.setCatalogId(id);
				cat = getAllProduct(cat);
				catalogList.add(cat);
			}
			
			statement.close();
			DBConnection.closeConnection(con);
			
			
			return catalogList;
			
			
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}

	
	private SearchedCategory getAllProduct(SearchedCategory cat) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		ResultSet res = null;
		try {
			statement = con.prepareCall("{?=call CATALOG_PKG.GET_CATEGORY_PRODUCT(?)}");
			statement.registerOutParameter(1, OracleTypes.CURSOR);
			statement.setString(2,cat.getCatalogId());
			statement.execute();
			OracleCallableStatement ocstm = (OracleCallableStatement) statement;
			res = ocstm.getCursor(1);
			while(res.next()){
				Product p  = new Product(res.getInt(1), res.getString(2), res.getString(3), res.getDouble(4));
				
				cat.addProduct(p);
			}
			statement.close();
			
			statement = con.prepareCall("{?=call CATALOG_PKG.GET_CATALOG_CRITERIA(?)}");
			statement.registerOutParameter(1, OracleTypes.CURSOR);
			statement.setString(2,cat.getCatalogId());
			statement.execute();
			ocstm = (OracleCallableStatement) statement;
			res = ocstm.getCursor(1);
			
			while(res.next()){
				Criteria c = new Criteria();
				c.setId(res.getInt(1));
				c.setName(res.getString(2));
				cat.addCriteria(getSubCriteria(c));
			}
			statement.close();
			
			DBConnection.closeConnection(con);
			
			return cat;
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}
	
	private Criteria getSubCriteria( Criteria c)throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		CallableStatement statement1 = null;
		ResultSet res = null;
		ResultSet res2 = null;
		try {
			statement = con.prepareCall("{?=call CATALOG_PKG.GET_CRITERIA_SUBCRITER(?)}");
			statement.registerOutParameter(1, OracleTypes.CURSOR);
			statement.setInt(2,c.getId());
			statement.execute();
			OracleCallableStatement ocstm = (OracleCallableStatement) statement;
			res = ocstm.getCursor(1);
			
			while(res.next()){
				SubCriteria s = new SubCriteria(res.getString(2));
				s.setId(res.getInt(1));
				
				
				statement1 = con.prepareCall("{?=call CATALOG_PKG.GET_SUBCRITERIA_PRODUCT(?)}");
				statement1.registerOutParameter(1, OracleTypes.CURSOR);
				statement1.setInt(2,s.getId());
				statement1.execute();
				OracleCallableStatement ocstm1 = (OracleCallableStatement) statement1;
				res2 = ocstm1.getCursor(1);
				
				while(res2.next()){
					s.addProduct(res2.getString(1));
				}
				
				statement1.close();
				c.addSubCriteria(s);
			}
			statement.close();
			
			DBConnection.closeConnection(con);
			return c;
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
		
	}
	
	public void insertCatalog(String parentId, String name) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		try {
			statement = con.prepareCall("{call CATALOG_PKG.INSERT_CATALOG(?,?)}");
			statement.setString(1,name);
			statement.setString(2,parentId);
			
			statement.execute();
			
			statement.close();
			DBConnection.closeConnection(con);
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}

	public void updateCatalog(String id, String name) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		try {
			statement = con.prepareCall("{call CATALOG_PKG.UPDATE_CATALOG_NAME(?,?)}");
			statement.setString(1,id);
			statement.setString(2,name);
			statement.execute();
						
			statement.close();
			DBConnection.closeConnection(con);
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}

	
	public void delCatalog(String id) throws Exception{
		
		Connection con = DBConnection.getConnection();
		CallableStatement statement = null;
		try {
			statement = con.prepareCall("{call CATALOG_PKG.DELETE_CATALOG(?)}");
			statement.setString(1,id);
			statement.execute();
						
			statement.close();
			DBConnection.closeConnection(con);
			
		} catch (Exception e) {
			try {
				statement.close();
			} catch (Exception exception) {
			}
			try {
				DBConnection.closeConnection(con);
			} catch (Exception exception) {
			}
			throw e;
		}
	}

}
